# 导入模块
from openpyxl import Workbook,load_workbook
from copy import copy
# 加载Excel
def get_data(file):
    wb = load_workbook(file)
    ws = wb.active
    data = []
    for row in ws.iter_rows():
        data.append([cell.value for cell in row])
    wb.close()
    return data
def write_data(data,wb1):
    wb = Workbook()
    font_ws = wb1.active
    max_co = font_ws.max_column
    # 创建1-12月sheet
    for i in range(1,13):
        ws = wb.create_sheet(f'{i}月份',index=i-1)
        #添加表头
        for i in range(1,2):
            for j in range(1,max_co+1):
                cell = ws.cell(row=i,column=j)
                load_cell = font_ws.cell(row=i,column=j)
                cell.value = load_cell.value
                cell.font = copy(load_cell.font)
                cell.fill = copy(load_cell.fill)
                cell.border = copy(load_cell.border)
                cell.number_format = copy(load_cell.number_format)

        #判断员工生日属于哪一个月份 f'{i:02}'表示如果i不足两位则自动补全 row[2][10:14]
        for row in data[1:]:
            row[-1] = row[2][10:14]
            if f'{i:02}' == row[2][10:12]:
                ws.append(row)



    wb.save('员工生日.xlsx')


if __name__ == '__main__':
    file = '../data/员工信息.xlsx'
    wb1 = load_workbook('../data/员工信息.xlsx')
    data = get_data(file)
    write_data(data,wb1)

# 新建Excel

# 创建1-12月sheet

# 把加载的数据写入到对应的worksheet

# 保存新建Excel

